-- @owner: wang_zhengyuan
-- @date: 2022-02-19
-- @testpoint: 唯一索引结合where后面的过滤索引

--step1: 设置浮点数精度; expect:成功
set extra_float_digits=0;

--step2:建表;expect:成功
drop table if exists t_ustore_dql_cbo_index_002;
create table t_ustore_dql_cbo_index_002(
     col_1 integer,
     col_2 bigint,
     col_3 double precision,
     col_4 decimal(12,6),
     col_5 bool,
     col_6 char(30),
     col_7 varchar2(50),
     col_8 varchar(30),
     col_9 interval day to second,
     col_10 timestamp,
     col_11 date,
     col_12 date,
     col_13 timestamp without time zone,
     col_14 blob,
     col_15 clob,
     col_16 int[]
) with (storage_type=ustore);

--step3:创建序列;expect:成功
drop sequence if exists s_ustore_dql_cbo_index_1;
create sequence s_ustore_dql_cbo_index_1
    increment by 1 start with 10;

--step4:创建索引;expect:成功
drop index if exists idx_unique_opengauss_function_ustore_cbo_index_case_002;
create unique index idx_unique_opengauss_function_ustore_cbo_index_case_002
    on t_ustore_dql_cbo_index_002(col_1);
drop index if exists idx_opengauss_function_ustore_cbo_index_case_002;
create index idx_opengauss_function_ustore_cbo_index_case_002
    on t_ustore_dql_cbo_index_002(col_2);

--step5:插入数据;expect:成功
truncate table t_ustore_dql_cbo_index_002;
insert into t_ustore_dql_cbo_index_002
    values(1,s_ustore_dql_cbo_index_1.nextval,   1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1001010101001010',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dql_cbo_index_002
    values(2,s_ustore_dql_cbo_index_1.nextval,   1+445.255,98*0.99, false,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','5','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1001010101001010',rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dql_cbo_index_002
    values(3,s_ustore_dql_cbo_index_1.nextval,   1+445.255,98*0.99, false,lpad('abc','3','@'),lpad('abc','3','b'),rpad('abc','3','e'),(interval '4 5:07:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1001010101001010',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dql_cbo_index_002
    values(4,s_ustore_dql_cbo_index_1.nextval,   1+445.255,98*0.99,  true,lpad('abc','3','@'),lpad('abc','4','b'),rpad('abc','6','e'),(interval '4 5:07:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1001010101001010',rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');

begin
for i in 5 .. 30 loop
insert into t_ustore_dql_cbo_index_002 values(i,'1'||i,446.255,98*0.99,true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1001010101001010',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
end loop;
end;
/

--step6:唯一索引结合where后面的过滤索引;expect:成功
analyze t_ustore_dql_cbo_index_002;
select col_1,col_3,col_4 from t_ustore_dql_cbo_index_002 where col_2 >10 order by col_1;
select col_1,col_3,col_4 from t_ustore_dql_cbo_index_002 where col_2 <10 order by col_1;
select col_1,col_3,col_4 from t_ustore_dql_cbo_index_002 where col_2 =10 order by col_1;
select col_1,col_3,col_4 from t_ustore_dql_cbo_index_002 where col_2 !=10 order by col_1;
select col_1,col_3,col_4 from t_ustore_dql_cbo_index_002 where col_2 >=10 order by col_1;
select col_1,col_3,col_4 from t_ustore_dql_cbo_index_002 where col_2 <=10 order by col_1;
